﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading;
using System.Windows;

namespace wms.Common
{
    class DBHelper
    {
        //private static string server = ConfigurationManager.AppSettings["DBServer"];
        //private static string id = ConfigurationManager.AppSettings["DBId"];
        //private static string user = ConfigurationManager.AppSettings["DBUser"];
        //private static string pwd = ConfigurationManager.AppSettings["DBPwd"];

        //数据库连接字符串
        public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
        //private static SqlConnection connection = null;
        //private static string con = "Server="+ server + ";Database=" + id + ";uid="+ user + ";pwd=" + pwd + ";Connect Timeout=10";


        //public static SqlConnection GetConnection()
        //{
        //    Stopwatch watch = new Stopwatch();
        //    watch.Start();

        //    while (true)
        //    {
        //        try
        //        {
        //            if (connection == null || connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed)
        //            {
        //                connection = new SqlConnection(con);
        //                connection.Open();
        //                return connection;
        //            }
        //            else if (connection.State == ConnectionState.Connecting)
        //            {
        //                Thread.Sleep(1000);
        //                continue;
        //            }
        //            return connection;
        //        }
        //        catch (Exception)
        //        {
        //            if (watch.ElapsedMilliseconds >= 30 * 1000)
        //            {
        //                MessageBoxResult r = MessageBox.Show("无法连接到数据库，是否重新尝试？","提示",MessageBoxButton.YesNo);
        //                if(r == MessageBoxResult.No)
        //                {
        //                    Environment.Exit(0);
        //                }
        //                else
        //                {
        //                    Thread.Sleep(1000);
        //                    watch.Restart();
        //                }
        //            }
        //        }
        //    }
        //}

        public static DataTable GetDataTable(String sql)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataTable dt = new DataTable();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(sql, connection);
                    command.Fill(dt);
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
                return dt;
            }

        }

        public static object GetDataSingle(String sql, SqlParameter[] param = null)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    cmd.Parameters.Clear();

                    if (dt != null && dt.Rows.Count > 0)
                    {
                        var value = dt.Rows[0][0];
                        if (value != null && value == DBNull.Value)
                        {
                            return null;
                        }
                        else
                        {
                            return value;
                        }
                    }
                    return null;
                }
                catch (System.Data.SqlClient.SqlException e)
                {

                    throw e;
                }
                finally
                {
                    connection.Close();
                }

            }
        }

        public static Dictionary<string, object> GetDataOne(String sql, SqlParameter[] param = null)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    cmd.Parameters.Clear();

                    if (dt != null && dt.Rows.Count > 0)
                    {
                        Dictionary<string, object> dic = new Dictionary<string, object>();

                        foreach (DataColumn column in dt.Columns)
                        {
                            var value = dt.Rows[0][column.ColumnName];
                            dic.Add(column.ColumnName, value);
                        }

                        return dic;
                    }
                    return null;
                }
                catch (System.Data.SqlClient.SqlException e)
                {

                    throw e;
                }
                finally
                {
                    connection.Close();
                }

            }
        }

        public static Dictionary<string, object> GetDataOne(String sql, Dictionary<string, object> param)
        {
            return GetDataOne(sql, Utils.ParseToSqlParameter(param));
        }

        public static DataTable GetDataTable(String sql, SqlParameter[] param)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    cmd.Parameters.Clear();
                    return dt;
                }
                catch (InvalidOperationException)
                {
                    return new DataTable();
                }
                finally
                {
                    connection.Close();
                }


            }
        }

        public static DataTable GetDataTable(String sql, Dictionary<string, object> param)
        {
            return GetDataTable(sql, Utils.ParseToSqlParameter(param));
        }

        public static DataTable GetDataTable(String sql, SqlParameter[] param, string order)
        {
            if (order == null || order.Length == 0)
            {
                order = "id desc";
            }
            sql = "select * from (" + sql + ") tmp order by " + order;
            return GetDataTable(sql, param);
        }

        public static DataTable GetDataTable(String sql, Dictionary<string, object> param, string order)
        {
            return GetDataTable(sql, Utils.ParseToSqlParameter(param), order);
        }

        public static DataTable GetDataTable(String sql, SqlParameter[] param, string order, int top, int offset)
        {
            if (order == null || order.Length == 0)
            {
                order = "id desc";
            }
            sql = "select top " + top + " * from(select row_number()over(order by " + order + ")rownumber, *from(" + sql + ")a)a1 where rownumber > " + offset;

            return GetDataTable(sql, param);
        }

        public static DataTable GetDataTable(String sql, Dictionary<string, object> param, string order, int top, int offset)
        {
            return GetDataTable(sql, Utils.ParseToSqlParameter(param), order, top, offset);
        }

        public static int GetCount(String sql, SqlParameter[] param)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    sql = "select count(*) from (" + sql + ") as temp";
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    cmd.CommandType = CommandType.Text;
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    object value = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return value == null ? 0 : (int)value;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return 0;
                }
                finally
                {
                    connection.Close();
                }

            }
        }

        public static int GetCount(String sql, Dictionary<string, object> param)
        {
            return GetCount(sql, Utils.ParseToSqlParameter(param));
        }


        /// 执行非查询存储过程和SQL语句
        /// 增、删、改
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <param name="cmdType">Command类型</param>
        /// <returns>返回影响行数</returns>
        public static int ExcuteSQL(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    cmd.CommandType = cmdType;
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    int result = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return result;
                }
                catch (InvalidOperationException)
                {
                    return 0;
                }
                finally
                {
                    connection.Close();
                }

            }
        }

        /// 执行非查询存储过程和SQL语句
        /// 增、删、改
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <param name="cmdType">Command类型</param>
        /// <returns>返回影响行数</returns>
        public static int ExcuteSQL(string sql, Dictionary<string, object> param, CommandType cmdType = CommandType.Text)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    cmd.CommandType = CommandType.Text;
                    if (param != null && param.Count > 0)
                    {
                        cmd.Parameters.AddRange(Utils.ParseToSqlParameter(param));
                    }
                    int result = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return result;
                }
                catch (InvalidOperationException)
                {
                    return 0;
                }
                finally
                {
                    connection.Close();
                }

            }
        }

        /// 执行非查询存储过程和SQL语句
        /// 增
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <param name="cmdType">Command类型</param>
        /// <returns>返回自增标识符</returns>
        public static int? Insert(string sql, Dictionary<string, object> param, CommandType cmdType = CommandType.Text)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    sql += "; select @@IDENTITY";
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    cmd.CommandType = CommandType.Text;
                    if (param != null && param.Count > 0)
                    {
                        cmd.Parameters.AddRange(Utils.ParseToSqlParameter(param));
                    }
                    object r = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    try
                    {
                        return Decimal.ToInt32((decimal)r);
                    }
                    catch (Exception)
                    {
                        return null;
                    }
                }
                catch (InvalidOperationException)
                {
                    return null;
                }
                finally
                {
                    connection.Close();
                }

            }
        }
        //public static Dictionary<string, string> State()
        //{


        //    Dictionary<string, string> dic = new Dictionary<string, string>();
        //    try
        //    {
        //        if (connection == null || connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed)
        //        {
        //            connection = new SqlConnection(con);
        //            connection.Open();
        //        }
        //        string sql = "select 1";
        //        SqlCommand cmd = new SqlCommand(sql, connection);
        //        cmd.CommandType = CommandType.Text;
        //        cmd.CommandTimeout = 3; //超时时间3秒
        //        int value = (int)cmd.ExecuteScalar();
        //        if (value == 1)
        //        {
        //            dic["result"] = "success";
        //            dic["message"] = "";
        //        }
        //        else
        //        {
        //            dic["result"] = "error";
        //            dic["message"] = "";
        //            try
        //            {
        //                if (connection != null)
        //                {
        //                    connection.Close();
        //                }
        //            }
        //            finally
        //            {
        //                connection = null;
        //            }
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        dic["result"] = "error";
        //        dic["message"] = e.Message;
        //        try
        //        {
        //            if (connection != null)
        //            {
        //                connection.Close();
        //            }
        //        }
        //        finally
        //        {
        //            connection = null;
        //        }
        //    }
        //    return dic;
        //}
    }

}
